package com.itjin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itjin.model.domain.dto.StatDTO;
import com.itjin.model.domain.entity.LearningMaterial;
import com.itjin.model.domain.vo.LearningMaterialVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 学习资料数据访问层
 */
@Mapper
public interface LearningMaterialMapper extends BaseMapper<LearningMaterial> {

    /**
     * 根据ID查询资料详情
     */
    @Select("SELECT * FROM learning_materials WHERE id = #{id} AND is_delete = 0")
    LearningMaterial getMaterialById(@Param("id") Integer id);

    /**
     * 查询某个用户上传的资料
     */
    @Select("SELECT * FROM learning_materials WHERE uploader_id = #{uploaderId} AND is_delete = 0 ORDER BY create_time DESC")
    Page<LearningMaterial> getUserMaterials(Page<LearningMaterial> page, @Param("uploaderId") Integer uploaderId);

    /**
     * 查询用户待审核的资料
     */
    @Select("SELECT * FROM learning_materials WHERE uploader_id = #{uploaderId} AND audit_status = 0 AND is_delete = 0 ORDER BY create_time DESC")
    Page<LearningMaterial> getUserPendingMaterials(Page<LearningMaterial> page, @Param("uploaderId") Integer uploaderId);

    /**
     * 分页查询公开的学习资料列表（已审核通过）
     */
    @Select("<script>" +
            "SELECT lm.*, u.username as uploader_username " +
            "FROM learning_materials lm " +
            "LEFT JOIN user u ON lm.uploader_id = u.id " +
            "WHERE lm.audit_status = 1 AND lm.is_delete = 0 " +
            "<if test='collegeName != null and collegeName != \"\"'>" +
            "AND lm.college_name = #{collegeName} " +
            "</if>" +
            "<if test='subjectType != null and subjectType != \"\"'>" +
            "AND lm.subject_type = #{subjectType} " +
            "</if>" +
            "<if test='academicYear != null and academicYear != \"\"'>" +
            "AND lm.academic_year = #{academicYear} " +
            "</if>" +
            "<if test='semester != null'>" +
            "AND lm.semester = #{semester} " +
            "</if>" +
            "<if test='fileType != null and fileType != \"\"'>" +
            "AND lm.file_type = #{fileType} " +
            "</if>" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (lm.title LIKE CONCAT('%', #{keyword}, '%') OR lm.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "ORDER BY " +
            "<choose>" +
            "<when test='orderBy == \"download\"'>lm.download_count DESC, lm.create_time DESC</when>" +
            "<when test='orderBy == \"view\"'>lm.view_count DESC, lm.create_time DESC</when>" +
            "<otherwise>lm.create_time DESC</otherwise>" +
            "</choose>" +
            "</script>")
    Page<LearningMaterialVO> getPublicMaterials(Page<LearningMaterialVO> page,
                                                @Param("collegeName") String collegeName,
                                                @Param("subjectType") String subjectType,
                                                @Param("academicYear") String academicYear,
                                                @Param("semester") Integer semester,
                                                @Param("fileType") String fileType,
                                                @Param("keyword") String keyword,
                                                @Param("orderBy") String orderBy);

    // ==================== 管理员相关方法 ====================

    /**
     * 管理员分页查询所有学习资料（包含审核状态筛选）
     */
    @Select("<script>" +
            "SELECT lm.*, u.username as uploader_username " +
            "FROM learning_materials lm " +
            "LEFT JOIN user u ON lm.uploader_id = u.id " +
            "WHERE lm.is_delete = 0 " +
            "<if test='auditStatus != null'>" +
            "AND lm.audit_status = #{auditStatus} " +
            "</if>" +
            "<if test='collegeName != null and collegeName != \"\"'>" +
            "AND lm.college_name = #{collegeName} " +
            "</if>" +
            "<if test='subjectType != null and subjectType != \"\"'>" +
            "AND lm.subject_type = #{subjectType} " +
            "</if>" +
            "<if test='uploaderId != null'>" +
            "AND lm.uploader_id = #{uploaderId} " +
            "</if>" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (lm.title LIKE CONCAT('%', #{keyword}, '%') OR lm.description LIKE CONCAT('%', #{keyword}, '%') OR u.username LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "ORDER BY " +
            "<choose>" +
            "<when test='orderBy == \"audit_time\"'>lm.audit_time DESC NULLS LAST, lm.create_time DESC</when>" +
            "<when test='orderBy == \"download\"'>lm.download_count DESC, lm.create_time DESC</when>" +
            "<when test='orderBy == \"view\"'>lm.view_count DESC, lm.create_time DESC</when>" +
            "<otherwise>lm.create_time DESC</otherwise>" +
            "</choose>" +
            "</script>")
    Page<LearningMaterialVO> getAdminMaterials(Page<LearningMaterialVO> page,
                                               @Param("auditStatus") Integer auditStatus,
                                               @Param("collegeName") String collegeName,
                                               @Param("subjectType") String subjectType,
                                               @Param("uploaderId") Integer uploaderId,
                                               @Param("keyword") String keyword,
                                               @Param("orderBy") String orderBy);

    /**
     * 管理员查询待审核资料列表
     */
    @Select("SELECT lm.*, u.username as uploader_username " +
            "FROM learning_materials lm " +
            "LEFT JOIN user u ON lm.uploader_id = u.id " +
            "WHERE lm.audit_status = 0 AND lm.is_delete = 0 " +
            "ORDER BY lm.create_time ASC")
    Page<LearningMaterialVO> getPendingAuditMaterials(Page<LearningMaterialVO> page);

    /**
     * 管理员统计各审核状态的资料数量
     */
    @Select("SELECT " +
            "CASE audit_status " +
            "WHEN 0 THEN '待审核' " +
            "WHEN 1 THEN '审核通过' " +
            "WHEN 2 THEN '审核拒绝' " +
            "END as name, " +
            "COUNT(*) as count " +
            "FROM learning_materials " +
            "WHERE is_delete = 0 " +
            "GROUP BY audit_status")
    List<StatDTO> getAuditStatusStats();

    /**
     * 管理员统计每日上传数量（最近30天）
     */
    @Select("SELECT DATE(create_time) as name, COUNT(*) as count " +
            "FROM learning_materials " +
            "WHERE is_delete = 0 AND create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY name DESC")
    List<StatDTO> getDailyUploadStats();

    /**
     * 管理员统计热门下载资料（TOP 10）
     */
    @Select("SELECT lm.title as name, lm.download_count as count " +
            "FROM learning_materials lm " +
            "WHERE lm.audit_status = 1 AND lm.is_delete = 0 " +
            "ORDER BY lm.download_count DESC " +
            "LIMIT 10")
    List<StatDTO> getTopDownloadMaterials();

    /**
     * 管理员强制删除资料（物理删除）
     */
    @Update("DELETE FROM learning_materials WHERE id = #{id}")
    int forceDeleteMaterial(@Param("id") Integer id);

    // ==================== 原有方法 ====================

    /**
     * 按学科分类查询资料
     */
    @Select("SELECT * FROM learning_materials WHERE subject_type = #{subjectType} AND audit_status = 1 AND is_delete = 0 ORDER BY create_time DESC")
    Page<LearningMaterial> getMaterialsBySubject(Page<LearningMaterial> page, @Param("subjectType") String subjectType);

    /**
     * 增加下载次数
     */
    @Update("UPDATE learning_materials SET download_count = download_count + 1 WHERE id = #{id}")
    int incrementDownloadCount(@Param("id") Integer id);

    /**
     * 增加浏览次数
     */
    @Update("UPDATE learning_materials SET view_count = view_count + 1 WHERE id = #{id}")
    int incrementViewCount(@Param("id") Integer id);

    /**
     * 检查用户是否可以修改/删除资料（只有待审核状态才能修改）
     */
    @Select("SELECT COUNT(*) FROM learning_materials WHERE id = #{id} AND uploader_id = #{uploaderId} AND audit_status = 0 AND is_delete = 0")
    int canModifyMaterial(@Param("id") Integer id, @Param("uploaderId") Integer uploaderId);

    /**
     * 统计各类文件类型的数量
     */
    @Select("SELECT file_type as name, COUNT(*) as count FROM learning_materials WHERE audit_status = 1 AND is_delete = 0 GROUP BY file_type")
    List<StatDTO> getFileTypeStats();

    /**
     * 统计各个学科的资料数量
     */
    @Select("SELECT subject_type as name, COUNT(*) as count FROM learning_materials WHERE audit_status = 1 AND is_delete = 0 GROUP BY subject_type")
    List<StatDTO> getSubjectStats();

    /**
     * 统计各个学院的资料数量
     */
    @Select("SELECT college_name as name, COUNT(*) as count FROM learning_materials WHERE audit_status = 1 AND is_delete = 0 GROUP BY college_name")
    List<StatDTO> getCollegeStats();

    /**
     * 统计用户上传资料的各个文件类型数量
     */
    @Select("SELECT file_type as name, COUNT(*) as count FROM learning_materials WHERE uploader_id = #{userId} AND is_delete = 0 GROUP BY file_type")
    List<StatDTO> getUserFileTypeStats(@Param("userId") Integer userId);

    /**
     * 统计用户各个学科的资料数量
     */
    @Select("SELECT subject_type as name, COUNT(*) as count FROM learning_materials WHERE uploader_id = #{userId} AND is_delete = 0 GROUP BY subject_type")
    List<StatDTO> getUserSubjectStats(@Param("userId") Integer userId);

    /**
     * 统计用户上传资料的总大小
     */
    @Select("SELECT SUM(file_size) as totalSize FROM learning_materials WHERE uploader_id = #{userId} AND is_delete = 0")
    Long getUserTotalSize(@Param("userId") Integer userId);
}